<?php
/**
 *
 * @author nhan
 *
 */
class Dao_ProductDAO extends Dao_AbstractDAO {

	public function __construct($db = null) {
		parent::__construct($db);
		$this->tableName = "product";
		$this->key = "id";
		$this->classModel = "Model_Product";
	}

	/**
	 * get product
	 * @param string $number
	 * @return multitype:unknown
	 */
	public function getListProducts($page, $number = null) {
		$query = $this->createSQLGetProduct();
		$query = $this->appendLimit($query, $page, $number);
		return $this->getList($query);
	}

	/**
	 * get product By Class Product And Category
	 * @param string $number
	 * @return multitype:unknown
	 */
	public function getListProductsByClassAndCat($idCat, $idClass, $page, $number = null, $search = null) {
		$query = $this->createSQLGetProductByClassAndCat($idCat, $idClass);
		$query .= $this->createSQLGetSearchQuery($search);
		$query = $this->appendLimit($query, $page, $number);
		return $this->getList($query);
	}

	/**
	 * get count of product by condition
	 */
	public function getCountProductsByCond($idCat, $idClass, $getMoreColumn = null, $search = null) {
		$query = $this->createSQLGetProductByOptionCond($idCat, $idClass, $getMoreColumn);
		$query .= $this->createSQLGetSearchQuery($search);
		return $this->getCount($query);

	}

	/**
	 * get product By Id
	 * @param string $idProduct
	 * @return multitype:unknown
	 */
	public function getProductById($idProduct) {
		$query = $this->createSQLGetProductById($idProduct);
		return $this->getList($query);
	}

	/**
	 * get count of product
	 */
	public function getCountProducts() {
		$query = $this->createSQLGetProduct();
		return $this->getCount($query);

	}

	/**
	 * get product By Condition
	 * @param array $cond
	 * @return multitype:unknown
	 */
	public function getListProductsByCondition($cond) {
		$query = $this->createSQLGetProductByCondition($cond);
		//$query = $this->appendLimit($query, $page, $number);
		return $this->getList($query);
	}

	private function createSQLCountProduct() {
		$query = " SELECT count(*) as cnt FROM $this->tableName T ";
		$query .= " WHERE ";
		$query .= " T.delete_flg = '0' ";
		return $query;
	}

	private function createSQLGetProduct() {
		$query = " SELECT * FROM $this->tableName T ";
		$query .= " WHERE ";
		$query .= " T.delete_flg = '0' ";
		return $query;
	}

	private function createSQLGetProductByClassAndCat($idCat, $idClass) {
		$query = " SELECT t1.*, t2.name_class, t2.name_class_folder ";
		$query .= " FROM $this->tableName t1 ";
		$query .= " left join productclass t2 on t1.id_product_class = t2.id ";
		$query .= " left join category t3 on t2.id_category = t3.id ";

		$query .= " WHERE ";
		$query .= " t1.delete_flg = '0' ";
		$query .= " AND t2.name_class != '' ";
		$query .= " AND t3.name != '' ";

		if($idCat != null && $idCat != '') {
			$query .= " AND t3.id = '$idCat' ";
		}

		if($idClass != null && $idClass != '') {
			$query .= " AND t2.id = '$idClass' ";
		}

		return $query;
	}

	private function createSQLGetSearchQuery($search) {
		$query = " ";

		if($search != null && $search != '' && $search != 'all') {
			$search = $this->removeEscape($search);
			$query .= " AND ( ";
			$query .= " t1.name like '%$search%' ";

			if (is_numeric ($search)) {
				$maxPrice = (double)$search + 1000000;
				$minPrice = (double)$search - 1000000;
				$maxYear  = (int)$search + 5;
				$minYear  = (int)$search - 5;
				$query .= " OR ( t1.price >= $minPrice AND t1.price <= $maxPrice ) ";
				$query .= " OR ( t1.year_of_manufacture >= $minYear AND t1.year_of_manufacture <= $maxYear ) ";
			}

			$query .= " OR ( t1.detail like '%$search%' ) ";
			$query .= " OR ( t1.origin like '$search%' ) ";
			$query .= " OR ( t1.level like '$search%' ) ";
			$query .= " OR ( t2.name_class like '$search%' ) ";

			$query .= " ) ";
		}

		return $query;
	}

	private function createSQLGetProductByOptionCond($idCat, $idClass, $getMoreColumn) {
		$query = " SELECT * ";

		if (isset($getMoreColumn) && $getMoreColumn !== '') {
			$query .= $getMoreColumn;
		}

		$query .= " FROM product t1 ";
		$query .= " left join productclass t2 on t1.id_product_class = t2.id ";
		$query .= " left join category t3 on t2.id_category = t3.id ";

		$query .= " WHERE ";
		$query .= " t1.delete_flg = '0' ";
		$query .= " AND t2.name_class != '' ";
		$query .= " AND t3.name != '' ";

		if($idCat != null && $idCat != '') {
			$query .= " AND t3.id = '$idCat' ";
		}

		if($idClass != null && $idClass != '') {
			$query .= " AND t2.id = '$idClass' ";
		}

		return $query;
	}

	private function createSQLGetProductById($idProduct) {
		$query = " SELECT t1.*, t2.name_class, t2.name_class_folder ";
		$query .= " FROM $this->tableName t1 ";
		$query .= " left join productclass t2 on t1.id_product_class = t2.id ";
		$query .= " left join category t3 on t2.id_category = t3.id ";

		$query .= " WHERE ";
		$query .= " t1.delete_flg = '0' ";
		$query .= " AND t2.name_class != '' ";
		$query .= " AND t3.name != '' ";
		$query .= " AND t1.id = '$idProduct' ";

		return $query;
	}

	public function getProductWithoutDelete($start = null, $number = null) {
		$query = $this->createSQLGetProductWithoutDelete($start, $number);
		$query = $this->appendLimit($query, $start, $number);
		return $this->getList($query);
	}

	public function countProductWithoutDelete() {
		$query = $this->createSQLCountProductWithoutDelete();
		$res = $this->query($query);
		if (count($res) > 0) {
			return $res[0]['CNT'];
		} else {
			return 0;
		}
	}

	private function createSQLGetProductWithoutDelete($start = null, $number = null) {
		$query = " SELECT T.*, prclss.name_class AS name_class, cate.name as category_name FROM $this->tableName T ";
		$query .= " LEFT OUTER JOIN productclass prclss ";
		$query .= " ON prclss.id = T.id_product_class ";
		$query .= " LEFT OUTER JOIN category cate ";
		$query .= " ON cate.id = T.id_category ";
		return $query;
	}

	private function createSQLCountProductWithoutDelete() {
		$query = " SELECT COUNT(*) AS CNT FROM $this->tableName T ";
		return $query;
	}

	private function createSQLGetProductByCondition($cond) {
		$query = " SELECT t1.*, t2.name_class, t2.name_class_folder ";
		$query .= " FROM $this->tableName t1 ";
		$query .= " left join productclass t2 on t1.id_product_class = t2.id ";
		$query .= " left join category t3 on t2.id_category = t3.id ";

		$query .= " WHERE ";
		$query .= " t1.delete_flg = '0' ";
		$query .= " AND t2.name_class != '' ";
		$query .= " AND t3.name != '' ";

		if (isset($cond['special_range'])) {
			$specialParam = $cond['special_range'];
			$query .= " AND t1.special_range = '$specialParam' ";
		}

		return $query;
	}

	/**
	 *
	 * @param String $id
	 * @return Model_Product
	 */
	public function getProductObjById($id) {
		$arr = $this->getProductById($id);
		if (count($arr) > 0) {
			return $arr[0];
		} else {
			return null;
		}
	}

	public function insertProduct(Model_Product $product){
		if ($product === null) {
			return false;
		}

		$this->startTransaction();
		$success = $this->insertModel($product);

		if ($success) {
			$this->commitTransaction();
			return true;
		} else {
			$this->rollBackTransaction();
			return false;
		}

		return $success;
	}

	public function updateProduct(Model_Product $product){
		if ($product === null) {
			return false;
		}
		$success = false;

		$sql = $this->startTransaction();

		$success = $this->updateModelById($product);

		if ($success) {
			$this->commitTransaction();
		} else {
			$this->rollBackTransaction();
		}

		return $success;
	}

	public function insertOrUpdate(Model_Product $product) {
		$success = false;
		if ($product->getId() !== null) {

			$success = $this->updateProduct($product);
		} else {
			$success = $this->insertProduct($product);
		}
		return $success;
	}

}